﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

/// <summary>
/// Summary description for SQLQuery
/// </summary>
public class SQLQuery
{
	public SQLQuery()
	{
		//
		// TODO: Add constructor logic here
		//
	}
    public static string SQLQuery_SELECT(string[] TableName, string[] Fields, string Criterias, string Priorities)
    {
        if (TableName != null && TableName.Length != 0)
        {
            string tableTable = (TableName.Length > 1 ? string.Join(", ", TableName) : TableName[0]);
        }
        string fields = (Fields != null && Fields.Length != 0 ? string.Join(", ", Fields) : "*");
        string criterias = (Criterias != null ? " WHERE " + Criterias : string.Empty);
        string priorities = (Priorities != null ? " ORDER BY " + Priorities : string.Empty);

        return string.Format("SELECT {0} FROM {1}{2}{3}", fields, TableName, criterias, priorities);
    }

    public static string SQLQuery_DELETE(string TableName, string Criterias)
    {
        string criterias = (Criterias != null ? " WHERE " + Criterias : string.Empty);
        return string.Format("DELETE {0} {1}", TableName, criterias);
    }

    public static string SQLQuery_INSERT(string TableName, string[] FieldNames, string[] FieldValues)
    {
        FormatSQLString(ref FieldNames, ref FieldValues);
        string fieldNames = string.Join(", ", FieldNames);
        string fieldValues = string.Join(", ", FieldValues);

        return string.Format("INSERT INTO {2}({0}) VALUES ({1})", fieldNames, fieldValues, TableName);
    }

    public static string SQLQuery_UPDATE(string TableName, string[] FieldNames, string[] FieldValues, string Criterias)
    {

        FormatSQLString(ref FieldNames, ref FieldValues);

        string[] setstr = new string[FieldNames.Length];
        for (int i = 0; i < FieldNames.Length; i++)
            setstr[i] = FieldNames[i] + "=" + FieldValues[i];

        string fieldnames = string.Join(" ,", setstr);

        string criterias = (Criterias != null ? " WHERE " + Criterias : string.Empty);

        return string.Format("UPDATE {1} SET {0}{2}", fieldnames, TableName, criterias);
    }
    //ham thay doi tham so
    private static void FormatSQLString(ref string[] Field, ref string[] Value)
    {
        if (Field != null && Value != null)
            for (int i = 0; i < Field.Length; i++)
            {
                Field[i] = Field[i].ToLower().Trim();
                if (Field[i][0] == '@')
                {
                    Field[i] = Field[i].Substring(1);
                    Value[i] = EscapeQuote(Value[i]);
                }
                else if (Field[i][1] == '@')
                {
                    if (Field[i][0] == 'N' || Field[i][0] == 'n')
                    {
                        Field[i] = Field[i].Substring(2);
                        Value[i] = EscapeUnicode(Value[i]);
                    }
                    else
                    {
                        if (Field[i][0] == 'D' || Field[i][0] == 'd')
                        {
                            Field[i] = Field[i].Substring(2);
                            Value[i] = EscapeSmallDateTime(Value[i]);
                        }
                    }
                }
                if (Field[i][0] != '[')
                    Field[i] = EscapeName(Field[i]);
            }
    }
    # region Escape Code

    public static int IsNull(object obValue, int intDefault)
    {
        return isnull(obValue) ? intDefault : (int)obValue;
    }

    public static string GetIDSet(string IDs)
    {
        if (IDs == null)
            return null;

        return "(" + IDs.Trim(new char[] { ' ', ',' }) + ")";
    }

    public static bool isnull(object obValue)
    {
        return (obValue == null || obValue == System.DBNull.Value);
    }

    public static string EscapeName(string S)
    {
        if (S.IndexOfAny(new char[] { '[', ']', '*', '.', ' ' }) != -1)
            return S;
        else
            return "[" + S + "]";
    }

    public static string EscapeQuote(string S)
    {
        return "'" + S.Trim().Replace("'", "''") + "'";
    }

    public static string EscapeQuoteButNotTrim(string S)
    {
        return "'" + S.Replace("'", "''") + "'";
    }

    public static string EscapeQuote(DateTime D)
    {
        return D.ToString("\\'MM-dd-yyyy HH:mm:ss\\'");
    }

    public static string EscapeSmallDateTime(string strDateTime)
    {
        return DateTime.Parse(strDateTime).ToString("\\'MM-dd-yyyy\\'");
    }

    public static string EscapeQuoteDate(string strDateTime)
    {
        return EscapeQuote(DateTime.Parse(strDateTime));
    }


    public static string ParseInt(string s)
    {
        try
        {
            s = s.Trim();
            if (s == string.Empty)
                return "NULL";
            else
                return int.Parse(s).ToString();
        }
        catch (Exception)
        {
            return "NULL";
        }
    }

    public static string ParseDouble(string s)
    {
        try
        {
            s = s.Trim();
            if (s == string.Empty)
                return "NULL";
            else
                return double.Parse(s).ToString();
        }
        catch (Exception)
        {
            return "NULL";
        }
    }

    public static string EscapeBoolBit(string Str)
    {
        return "(" + Str + ")";
    }

    public static string EscapeUnicode(string Str)
    {
        return "N" + EscapeQuote(Str);
    }

    public static string EscapeQuoteUnicode(string strUni)
    {
        return EscapeUnicode(strUni);
    }

    # endregion
}